package SQLConnection;

import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 均先生
 * Date: 2023-11-28
 * Time: 16:08
 */
public class Conn {
    Connection connection;
    public void init() {
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource) dataSource).setUrl("jdbc:mysql://localhost:3306/ebook");
        ((MysqlDataSource) dataSource).setUser("root");
        ((MysqlDataSource) dataSource).setPassword("123456");
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void addBorrow_Info(int student_id,int book_id,String start_time,String end_time) {
        init();
        String sql = "insert into borrow_info (book_id,start_id,end_time) values (?,?,?,?) where student_id = ?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,book_id);
            statement.setString(2,start_time);
            statement.setString(3,end_time);
            statement.setInt(4,student_id);
            int n = statement.executeUpdate();
            System.out.println("共"+n+"行受到影响");
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void addBook(String book_name,String author,BigDecimal price,int category_id) {
        init();
        String sql = "insert into book (name,author,price,category_id) values (?,?,?,?)";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1,book_name);
            statement.setString(2,author);
            statement.setBigDecimal(3,price);
            statement.setInt(4,category_id);
            int n = statement.executeUpdate();
            System.out.println("共"+n+"行受到影响");
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void updatePriceByAuthor(BigDecimal price,String author) {
        init();
        String sql = "update book set price = ? where author = ? ";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setBigDecimal(1,price);
            statement.setString(2,author);
            int n = statement.executeUpdate();
            System.out.println("共"+n+"收到影响");
            //释放资源
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void updatePriceByName(BigDecimal price,String name) {
        init();
        String sql = "update book set price = ? where name = ? ";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setBigDecimal(1,price);
            statement.setString(2,name);
            int n = statement.executeUpdate();
            System.out.println("共"+n+"收到影响");
            //释放资源
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void delBorrow_infoByMax_id(int id) {
        init();
        String sql = "delete from borrow_info where id = ? limit 1";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            int n = statement.executeUpdate();
            System.out.println("共"+n+"收到影响");
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void delBookByName(String book_name) {
        init();
        String sql = "delete from book where name = ?";
        try {
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1,book_name);
        } catch (SQLException e) {
                e.printStackTrace();
        }
    }

}
